<!DOCTYPE HTML>
<html>

<head>
	<link rel="bookmark"  type="image/x-icon"  href="/img/logo_miccall.png"/>
	<link rel="shortcut icon" href="/img/logo_miccall.png">
	
			    <title>
    Ame雨
    </title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" />
    <link rel="stylesheet" href="/amelin-ting/css/mic_main.css" />
    <link rel="stylesheet" href="/amelin-ting/css/dropdownMenu.css" />
    <meta name="keywords" content="Ame雨" />
    
    	<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
	 
    <noscript>
        <link rel="stylesheet" href="/amelin-ting/css/noscript.css" />
    </noscript>
    <style type="text/css">
        body:before {
          content: ' ';
          position: fixed;
          top: 0;
          background: url('/amelin-ting/img/bg.jpg') center 0 no-repeat;
          right: 0;
          bottom: 0;
          left: 0;
          background-size: cover; 
        }
    </style>

			    
  
    <script type="text/x-mathjax-config">
      MathJax.Hub.Config({
        tex2jax: {
          inlineMath: [ ['$','$'], ["\\(","\\)"]  ],
          processEscapes: true,
          skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code']
        }
      });
    </script>

    <script type="text/x-mathjax-config">
      MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax(), i;
        for (i=0; i < all.length; i += 1) {
          all[i].SourceElement().parentNode.className += ' has-jax';
        }
      });
    </script>
    <script async type="text/javascript" src="//cdn.bootcss.com/mathjax/2.7.1/latest.js?config=TeX-AMS-MML_HTMLorMML"></script>
  


    <script src="/amelin-ting/js/jquery.min.js"></script>
    <script src="/amelin-ting/js/jquery.scrollex.min.js"></script>
    <script src="/amelin-ting/js/jquery.scrolly.min.js"></script>
    <script src="/amelin-ting/js/skel.min.js"></script>
    <script src="/amelin-ting/js/util.js"></script>
    <script src="/amelin-ting/js/main.js"></script>
	
<meta name="generator" content="Hexo 5.4.0"></head>
    
		
<!-- Layouts -->



<!--  代码渲染  -->
<link rel="stylesheet" href="/css/prism_coy.css" />
<link rel="stylesheet" href="/css/typo.css" />
<!-- 文章页 -->
<body class="is-loading">
    <!-- Wrapper 外包 s-->
    <div id="wrapper" class="fade-in">
        <!-- Intro 头部显示 s -->
        <!-- Intro 头部显示 e -->
        <!-- Header 头部logo start -->
        <header id="header">
    <a href="/amelin-ting/" class="logo">Art Space</a>
</header>
        <!-- Nav 导航条 start -->
        <nav id="nav" class="special" >
            <ul class="menu links" >
			<!-- Homepage  主页  --> 
			<li >
	            <a href="/amelin-ting/" rel="nofollow">主页</a>
	        </li>
			<!-- categories_name  分类   --> 
	        
	        <li class="active">
	            <a href="#s1">分类</a>
	                    <ul class="submenu">
	                        <li>
	                        <a class="category-link" href="/amelin-ting/categories/Go/">Go</a></li><li><a class="category-link" href="/amelin-ting/categories/django/">django</a></li><li><a class="category-link" href="/amelin-ting/categories/drf/">drf</a></li><li><a class="category-link" href="/amelin-ting/categories/gin/">gin</a></li><li><a class="category-link" href="/amelin-ting/categories/go/">go</a></li><li><a class="category-link" href="/amelin-ting/categories/linux/">linux</a></li><li><a class="category-link" href="/amelin-ting/categories/mysql/">mysql</a></li><li><a class="category-link" href="/amelin-ting/categories/python/">python</a></li><li><a class="category-link" href="/amelin-ting/categories/%E5%86%85%E7%BD%91%E7%A9%BF%E9%80%8F/">内网穿透</a></li><li><a class="category-link" href="/amelin-ting/categories/%E5%89%8D%E7%AB%AF/">前端</a>
	                    </ul>
	        </li>
	        
	        <!-- archives  归档   --> 
	        
	        
		        <!-- Pages 自定义   -->
		        
		        <li>
		            <a href="/about/" title="简历">
		                简历
		            </a>
		        </li>
		        
		        <li>
		            <a href="/group/" title="团队">
		                团队
		            </a>
		        </li>
		        
		        <li>
		            <a href="/gallery/" title="图库">
		                图库
		            </a>
		        </li>
		        
		        <li>
		            <a href="/tag/" title="标签">
		                标签
		            </a>
		        </li>
		        


            </ul>
            <!-- icons 图标   -->
			<ul class="icons">
                    
                    <li>
                        <a title="github" href="https://github.com/miccall" target="_blank" rel="noopener">
                            <i class="icon fa fa-github"></i>
                        </a>
                    </li>
                    
                    <li>
                        <a title="500px" href="http://500px.com" target="_blank" rel="noopener">
                            <i class="icon fa fa-500px"></i>
                        </a>
                    </li>
                    
			</ul>
</nav>

        <div id="main" >
            <div class ="post_page_title_img" style="height: 25rem;background-image: url(https://img2.baidu.com/it/u=3450341524,2316378230&amp;fm=26&amp;fmt=auto);background-position: center; background-repeat:no-repeat; background-size:cover;-moz-background-size:cover;overflow:hidden;" >
                <a href="#" style="padding: 4rem 4rem 2rem 4rem ;"><h2 >Go连接mysql</h2></a>
            </div>
            <!-- Post -->
            <div class="typo" style="padding: 3rem;">
                <h2 id="一、下载MySQL依赖"><a href="#一、下载MySQL依赖" class="headerlink" title="一、下载MySQL依赖"></a>一、下载MySQL依赖</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">// Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。使用database/sql包时必须注入（至少）一个数据库驱动。</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">go</span> get -u github.com/<span class="keyword">go</span>-sql-driver/mysql</span><br></pre></td></tr></table></figure>

<h2 id="二、Go连接MySQL"><a href="#二、Go连接MySQL" class="headerlink" title="二、Go连接MySQL"></a>二、Go连接MySQL</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> main</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> (</span><br><span class="line">	<span class="string">&quot;database/sql&quot;</span></span><br><span class="line">	<span class="string">&quot;fmt&quot;</span></span><br><span class="line"></span><br><span class="line">	_ <span class="string">&quot;github.com/go-sql-driver/mysql&quot;</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">main</span><span class="params">()</span></span> &#123;</span><br><span class="line">	<span class="comment">// 数据库信息</span></span><br><span class="line">	dsn := <span class="string">&quot;root:123456@tcp(127.0.0.1:3306)/djangobbs&quot;</span></span><br><span class="line">	<span class="comment">// 连接数据库</span></span><br><span class="line">	db, err := sql.Open(<span class="string">&quot;mysql&quot;</span>, dsn) <span class="comment">// 这里不会检验账号密码是否正确哦</span></span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123; <span class="comment">// dsn格式不正确的时候就会报错</span></span><br><span class="line">		fmt.Println(<span class="string">&quot;err: &quot;</span>,err)</span><br><span class="line">         <span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	err = db.Ping() <span class="comment">// 这里才会检验账号密码</span></span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;db.err:&quot;</span>, err)</span><br><span class="line">         <span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	fmt.Println(<span class="string">&quot;连接成功&quot;</span>)</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h2 id="三、增删查改"><a href="#三、增删查改" class="headerlink" title="三、增删查改"></a>三、增删查改</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">package</span> main</span><br><span class="line"></span><br><span class="line"><span class="keyword">import</span> (</span><br><span class="line">	<span class="string">&quot;database/sql&quot;</span></span><br><span class="line">	<span class="string">&quot;fmt&quot;</span></span><br><span class="line">	_<span class="string">&quot;github.com/go-sql-driver/mysql&quot;</span></span><br><span class="line">)</span><br><span class="line"></span><br><span class="line"><span class="keyword">var</span> db *sql.DB <span class="comment">// 连接池对象</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">type</span> User <span class="keyword">struct</span>&#123;</span><br><span class="line">	id <span class="keyword">int</span></span><br><span class="line">	name <span class="keyword">string</span></span><br><span class="line">	age <span class="keyword">int</span></span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">initMySQL</span><span class="params">()</span> <span class="params">(err error)</span></span> &#123;</span><br><span class="line">	dsn := <span class="string">&quot;root:123456@tcp(127.0.0.1:3306)/go&quot;</span></span><br><span class="line">	db, err = sql.Open(<span class="string">&quot;mysql&quot;</span>, dsn) </span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123; </span><br><span class="line">		fmt.Println(<span class="string">&quot;err: &quot;</span>,err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	err = db.Ping() </span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;db.err:&quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	db.SetMaxOpenConns(<span class="number">10</span>) <span class="comment">// 设置数据库连接的最大连接数</span></span><br><span class="line">	db.SetMaxIdleConns(<span class="number">5</span>) <span class="comment">// 设置最大空连接数</span></span><br><span class="line">	<span class="keyword">return</span>	</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 查询单条数据</span></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">selectOneData</span><span class="params">(id <span class="keyword">int</span>)</span></span> &#123;</span><br><span class="line">	sqlStr := <span class="string">&quot;select * from userinfo where id=?&quot;</span></span><br><span class="line">	<span class="keyword">var</span> u User</span><br><span class="line">	<span class="comment">// 非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放</span></span><br><span class="line">	err := db.QueryRow(sqlStr, id).Scan(&amp;u.id, &amp;u.name, &amp;u.age)</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Printf(<span class="string">&quot;scan failed, err:%v\n&quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	fmt.Printf(<span class="string">&quot;id:%d name:%s age:%d\n&quot;</span>, u.id, u.name, u.age)</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 查询多条数据</span></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">selectAllData</span><span class="params">(i <span class="keyword">int</span>)</span></span> &#123;</span><br><span class="line">	<span class="comment">// 写出sql语句</span></span><br><span class="line">	sqlstr := <span class="string">`select * from userinfo where id &gt; ?`</span></span><br><span class="line">	<span class="comment">// 执行</span></span><br><span class="line">	rows, err := db.Query(sqlstr, i)</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;exec.err:&quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	<span class="comment">// 一定要关闭rows</span></span><br><span class="line">	<span class="keyword">defer</span> rows.Close()</span><br><span class="line">	<span class="comment">// 循环取值</span></span><br><span class="line">	<span class="keyword">for</span> rows.Next() &#123;</span><br><span class="line">		<span class="keyword">var</span> u1 User</span><br><span class="line">		err := rows.Scan(&amp;u1.id, &amp;u1.name, &amp;u1.age)</span><br><span class="line">		<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">			fmt.Println(<span class="string">&quot;err:&quot;</span>, err)</span><br><span class="line">			<span class="keyword">return</span></span><br><span class="line">		&#125;</span><br><span class="line">		fmt.Printf(<span class="string">&quot;u1: %#v\n&quot;</span>, u1)</span><br><span class="line">	&#125;</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 增加一条数据</span></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">insert</span><span class="params">()</span></span> &#123;</span><br><span class="line">	<span class="comment">// 写sql语句</span></span><br><span class="line">	sqlStr := <span class="string">`insert into userinfo(name, age) values(&quot;vlicecream&quot;, 20)`</span></span><br><span class="line">	<span class="comment">// exec</span></span><br><span class="line">	ret, err := db.Exec(sqlStr)</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;exec: &quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	<span class="comment">// 如果是插入数据的操作，能够拿到操作数据的ID值</span></span><br><span class="line">	id, err := ret.LastInsertId()</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;iderr: &quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	fmt.Println(id)</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 更新一条数据</span></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">update</span><span class="params">(age, id <span class="keyword">int</span>)</span></span> &#123;</span><br><span class="line">	sqlStr := <span class="string">`update userinfo set age=? where id = ?`</span></span><br><span class="line">	<span class="comment">// exec</span></span><br><span class="line">	ret, err := db.Exec(sqlStr, age, id)</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;exec: &quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	id1, err := ret.RowsAffected()</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;iderr: &quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	fmt.Println(id1)</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="comment">// 删除一条数据</span></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">delete</span><span class="params">(id <span class="keyword">int</span>)</span></span> &#123;</span><br><span class="line">	sqlStr := <span class="string">`delete from userinfo where id = ?`</span></span><br><span class="line">	<span class="comment">// exec</span></span><br><span class="line">	ret, err := db.Exec(sqlStr, id)</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;exec: &quot;</span>, err)</span><br><span class="line">		<span class="keyword">return</span></span><br><span class="line">	&#125;</span><br><span class="line">	id1, err := ret.RowsAffected()</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(<span class="string">&quot;iderr: &quot;</span>, err)</span><br><span class="line">	&#125;</span><br><span class="line">	fmt.Println(id1)</span><br><span class="line">&#125;</span><br><span class="line"></span><br><span class="line"><span class="function"><span class="keyword">func</span> <span class="title">main</span><span class="params">()</span></span> &#123;</span><br><span class="line">	err := initMySQL()</span><br><span class="line">	<span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">		fmt.Println(err)</span><br><span class="line">	&#125;</span><br><span class="line">	fmt.Println(<span class="string">&quot;连接数据库成功&quot;</span>)</span><br><span class="line">	<span class="comment">// 查询单个数据</span></span><br><span class="line">	selectOneData(<span class="number">1</span>)</span><br><span class="line">	<span class="comment">// 查询大于0的数据</span></span><br><span class="line">	selectAllData(<span class="number">0</span>)</span><br><span class="line">	<span class="comment">// 增加一条数据</span></span><br><span class="line">	insert()</span><br><span class="line">	<span class="comment">// 更新一条数据</span></span><br><span class="line">	update(<span class="number">19</span>, <span class="number">1</span>)</span><br><span class="line">	<span class="comment">// 删除一条数据</span></span><br><span class="line">	<span class="built_in">delete</span>(<span class="number">3</span>)</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>

<h2 id="四、MySQL的预处理"><a href="#四、MySQL的预处理" class="headerlink" title="四、MySQL的预处理"></a>四、MySQL的预处理</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span> 什么是预处理？</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">    普通SQL语句执行过程：</span></span><br><span class="line"><span class="comment">    客户端对SQL语句进行占位符替换得到完整的SQL语句。</span></span><br><span class="line"><span class="comment">    客户端发送完整SQL语句到MySQL服务端</span></span><br><span class="line"><span class="comment">    MySQL服务端执行完整的SQL语句并将结果返回给客户端。</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="number">2.</span> 预处理执行过程：</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">    把SQL语句分成两部分，命令部分与数据部分。</span></span><br><span class="line"><span class="comment">    先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。</span></span><br><span class="line"><span class="comment">    然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。</span></span><br><span class="line"><span class="comment">    MySQL服务端执行完整的SQL语句并将结果返回给客户端。</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="number">3.</span> 为什么要预处理？</span><br><span class="line">    <span class="comment">// 优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。</span></span><br><span class="line">    <span class="comment">// 避免SQL注入问题。</span></span><br><span class="line"></span><br><span class="line"><span class="number">4.</span> 代码演示</span><br><span class="line">    <span class="comment">// 预处理方式插入多条数据</span></span><br><span class="line">    <span class="function"><span class="keyword">func</span> <span class="title">insertManyData</span><span class="params">()</span></span> &#123;</span><br><span class="line">        sqlStr := <span class="string">`insert into user(name, age) values(?, ?)`</span></span><br><span class="line">        stmt, err := db.Prepare(sqlStr)</span><br><span class="line">        <span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">            fmt.Println(<span class="string">&quot;stmt.err: &quot;</span>, err)</span><br><span class="line">        &#125;</span><br><span class="line">        <span class="keyword">defer</span> stmt.Close()</span><br><span class="line">        <span class="comment">// 后续只需要拿到stmt去操作</span></span><br><span class="line">        <span class="keyword">var</span> m = <span class="keyword">map</span>[<span class="keyword">string</span>]<span class="keyword">int</span>&#123;</span><br><span class="line">            <span class="string">&quot;一二三&quot;</span>: <span class="number">30</span>,</span><br><span class="line">            <span class="string">&quot;四五六&quot;</span>: <span class="number">40</span>,</span><br><span class="line">            <span class="string">&quot;ss&quot;</span>: <span class="number">33</span>,</span><br><span class="line">        &#125; </span><br><span class="line">        <span class="keyword">for</span> k, v := <span class="keyword">range</span> m &#123;</span><br><span class="line">            stmt.Exec(k, v)</span><br><span class="line">        &#125;</span><br><span class="line">    &#125;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h2 id="五、Go开启MySQL事务"><a href="#五、Go开启MySQL事务" class="headerlink" title="五、Go开启MySQL事务"></a>五、Go开启MySQL事务</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br></pre></td><td class="code"><pre><span class="line"><span class="number">1.</span> 什么是事务</span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">    一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">    在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="number">2.</span> 事务的ACID</span><br><span class="line">	<span class="comment">// 通常事务必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。</span></span><br><span class="line"></span><br><span class="line"><span class="number">3.</span> 代码演示</span><br><span class="line">	<span class="comment">// Go的事物靠 开始事务Begin()  提交事务Commit()   回滚事务Rollback()实现</span></span><br><span class="line">    <span class="function"><span class="keyword">func</span> <span class="title">transactionDemo</span><span class="params">()</span></span> &#123;</span><br><span class="line">        tx, err := db.Begin() <span class="comment">// 开启事务</span></span><br><span class="line">        <span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">            <span class="keyword">if</span> tx != <span class="literal">nil</span> &#123;</span><br><span class="line">                tx.Rollback() <span class="comment">// 回滚</span></span><br><span class="line">            &#125;</span><br><span class="line">            fmt.Printf(<span class="string">&quot;begin trans failed, err:%v\n&quot;</span>, err)</span><br><span class="line">            <span class="keyword">return</span></span><br><span class="line">        &#125;</span><br><span class="line">        sqlStr1 := <span class="string">&quot;Update user set age=30 where id=?&quot;</span></span><br><span class="line">        ret1, err := tx.Exec(sqlStr1, <span class="number">2</span>)</span><br><span class="line">        <span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">            tx.Rollback() <span class="comment">// 回滚</span></span><br><span class="line">            fmt.Printf(<span class="string">&quot;exec sql1 failed, err:%v\n&quot;</span>, err)</span><br><span class="line">            <span class="keyword">return</span></span><br><span class="line">        &#125;</span><br><span class="line">        affRow1, err := ret1.RowsAffected()</span><br><span class="line">        <span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">            tx.Rollback() <span class="comment">// 回滚</span></span><br><span class="line">            fmt.Printf(<span class="string">&quot;exec ret1.RowsAffected() failed, err:%v\n&quot;</span>, err)</span><br><span class="line">            <span class="keyword">return</span></span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        sqlStr2 := <span class="string">&quot;Update user set age=40 where id=?&quot;</span></span><br><span class="line">        ret2, err := tx.Exec(sqlStr2, <span class="number">3</span>)</span><br><span class="line">        <span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">            tx.Rollback() <span class="comment">// 回滚</span></span><br><span class="line">            fmt.Printf(<span class="string">&quot;exec sql2 failed, err:%v\n&quot;</span>, err)</span><br><span class="line">            <span class="keyword">return</span></span><br><span class="line">        &#125;</span><br><span class="line">        affRow2, err := ret2.RowsAffected()</span><br><span class="line">        <span class="keyword">if</span> err != <span class="literal">nil</span> &#123;</span><br><span class="line">            tx.Rollback() <span class="comment">// 回滚</span></span><br><span class="line">            fmt.Printf(<span class="string">&quot;exec ret1.RowsAffected() failed, err:%v\n&quot;</span>, err)</span><br><span class="line">            <span class="keyword">return</span></span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        fmt.Println(affRow1, affRow2)</span><br><span class="line">        <span class="keyword">if</span> affRow1 == <span class="number">1</span> &amp;&amp; affRow2 == <span class="number">1</span> &#123;</span><br><span class="line">            fmt.Println(<span class="string">&quot;事务提交啦...&quot;</span>)</span><br><span class="line">            tx.Commit() <span class="comment">// 提交事务</span></span><br><span class="line">        &#125; <span class="keyword">else</span> &#123;</span><br><span class="line">            tx.Rollback()</span><br><span class="line">            fmt.Println(<span class="string">&quot;事务回滚啦...&quot;</span>)</span><br><span class="line">        &#125;</span><br><span class="line"></span><br><span class="line">        fmt.Println(<span class="string">&quot;exec trans success!&quot;</span>)</span><br><span class="line">    &#125;</span><br></pre></td></tr></table></figure>

<h2 id="六、sqlx使用"><a href="#六、sqlx使用" class="headerlink" title="六、sqlx使用"></a>六、sqlx使用</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br><span class="line">114</span><br><span class="line">115</span><br><span class="line">116</span><br><span class="line">117</span><br><span class="line">118</span><br><span class="line">119</span><br><span class="line">120</span><br><span class="line">121</span><br><span class="line">122</span><br><span class="line">123</span><br><span class="line">124</span><br><span class="line">125</span><br><span class="line">126</span><br><span class="line">127</span><br><span class="line">128</span><br><span class="line">129</span><br><span class="line">130</span><br><span class="line">131</span><br><span class="line">132</span><br><span class="line">133</span><br><span class="line">134</span><br><span class="line">135</span><br><span class="line">136</span><br><span class="line">137</span><br><span class="line">138</span><br><span class="line">139</span><br><span class="line">140</span><br><span class="line">141</span><br><span class="line">142</span><br><span class="line">143</span><br><span class="line">144</span><br><span class="line">145</span><br><span class="line">146</span><br><span class="line">147</span><br><span class="line">148</span><br><span class="line">149</span><br><span class="line">150</span><br><span class="line">151</span><br><span class="line">152</span><br><span class="line">153</span><br><span class="line">154</span><br><span class="line">155</span><br><span class="line">156</span><br><span class="line">157</span><br><span class="line">158</span><br><span class="line">159</span><br><span class="line">160</span><br><span class="line">161</span><br><span class="line">162</span><br><span class="line">163</span><br><span class="line">164</span><br><span class="line">165</span><br><span class="line">166</span><br><span class="line">167</span><br><span class="line">168</span><br><span class="line">169</span><br><span class="line">170</span><br><span class="line">171</span><br><span class="line">172</span><br><span class="line">173</span><br><span class="line">174</span><br><span class="line">175</span><br><span class="line">176</span><br><span class="line">177</span><br><span class="line">178</span><br><span class="line">179</span><br><span class="line">180</span><br><span class="line">181</span><br><span class="line">182</span><br><span class="line">183</span><br><span class="line">184</span><br><span class="line">185</span><br><span class="line">186</span><br><span class="line">187</span><br><span class="line">188</span><br><span class="line">189</span><br><span class="line">190</span><br><span class="line">191</span><br><span class="line">192</span><br><span class="line">193</span><br><span class="line">194</span><br><span class="line">195</span><br><span class="line">196</span><br><span class="line">197</span><br><span class="line">198</span><br><span class="line">199</span><br><span class="line">200</span><br><span class="line">201</span><br><span class="line">202</span><br><span class="line">203</span><br><span class="line">204</span><br><span class="line">205</span><br><span class="line">206</span><br><span class="line">207</span><br><span class="line">208</span><br><span class="line">209</span><br><span class="line">210</span><br><span class="line">211</span><br><span class="line">212</span><br><span class="line">213</span><br><span class="line">214</span><br><span class="line">215</span><br><span class="line">216</span><br><span class="line">217</span><br><span class="line">218</span><br><span class="line">219</span><br><span class="line">220</span><br><span class="line">221</span><br><span class="line">222</span><br><span class="line">223</span><br><span class="line">224</span><br><span class="line">225</span><br><span class="line">226</span><br><span class="line">227</span><br><span class="line">228</span><br><span class="line">229</span><br><span class="line">230</span><br><span class="line">231</span><br><span class="line">232</span><br><span class="line">233</span><br><span class="line">234</span><br><span class="line">235</span><br><span class="line">236</span><br><span class="line">237</span><br><span class="line">238</span><br><span class="line">239</span><br><span class="line">240</span><br><span class="line">241</span><br><span class="line">242</span><br><span class="line">243</span><br><span class="line">244</span><br><span class="line">245</span><br><span class="line">246</span><br><span class="line">247</span><br><span class="line">248</span><br><span class="line">249</span><br><span class="line">250</span><br><span class="line">251</span><br><span class="line">252</span><br><span class="line">253</span><br><span class="line">254</span><br><span class="line">255</span><br><span class="line">256</span><br><span class="line">257</span><br><span class="line">258</span><br><span class="line">259</span><br><span class="line">260</span><br><span class="line">261</span><br><span class="line">262</span><br><span class="line">263</span><br><span class="line">264</span><br><span class="line">265</span><br><span class="line">266</span><br><span class="line">267</span><br><span class="line">268</span><br><span class="line">269</span><br><span class="line">270</span><br><span class="line">271</span><br><span class="line">272</span><br><span class="line">273</span><br><span class="line">274</span><br><span class="line">275</span><br><span class="line">276</span><br><span class="line">277</span><br><span class="line">278</span><br><span class="line">279</span><br><span class="line">280</span><br><span class="line">281</span><br><span class="line">282</span><br><span class="line">283</span><br><span class="line">284</span><br><span class="line">285</span><br><span class="line">286</span><br><span class="line">287</span><br><span class="line">288</span><br><span class="line">289</span><br><span class="line">290</span><br><span class="line">291</span><br><span class="line">292</span><br><span class="line">293</span><br><span class="line">294</span><br><span class="line">295</span><br><span class="line">296</span><br><span class="line">297</span><br><span class="line">298</span><br><span class="line">299</span><br><span class="line">300</span><br><span class="line">301</span><br><span class="line">302</span><br><span class="line">303</span><br><span class="line">304</span><br><span class="line">305</span><br><span class="line">306</span><br><span class="line">307</span><br><span class="line">308</span><br><span class="line">309</span><br><span class="line">310</span><br><span class="line">311</span><br><span class="line">312</span><br><span class="line">313</span><br><span class="line">314</span><br><span class="line">315</span><br><span class="line">316</span><br><span class="line">317</span><br><span class="line">318</span><br><span class="line">319</span><br><span class="line">320</span><br><span class="line">321</span><br><span class="line">322</span><br><span class="line">323</span><br><span class="line">324</span><br><span class="line">325</span><br><span class="line">326</span><br><span class="line">327</span><br><span class="line">328</span><br><span class="line">329</span><br><span class="line">330</span><br><span class="line">331</span><br><span class="line">332</span><br><span class="line">333</span><br><span class="line">334</span><br><span class="line">335</span><br><span class="line">336</span><br><span class="line">337</span><br><span class="line">338</span><br><span class="line">339</span><br><span class="line">340</span><br><span class="line">341</span><br><span class="line">342</span><br><span class="line">343</span><br><span class="line">344</span><br><span class="line">345</span><br><span class="line">346</span><br><span class="line">347</span><br><span class="line">348</span><br><span class="line">349</span><br><span class="line">350</span><br><span class="line">351</span><br><span class="line">352</span><br><span class="line">353</span><br><span class="line">354</span><br><span class="line">355</span><br><span class="line">356</span><br><span class="line">357</span><br><span class="line">358</span><br><span class="line">359</span><br><span class="line">360</span><br><span class="line">361</span><br><span class="line">362</span><br><span class="line">363</span><br><span class="line">364</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">go</span> get github.com/jmoiron/sqlx</span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">在项目中我们通常可能会使用database/sql连接MySQL数据库。本文借助使用sqlx实现批量插入数据的例子，介绍了sqlx中可能被你忽视了的sqlx.In和DB.NamedExec方法。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">在项目中我们通常可能会使用database/sql连接MySQL数据库。sqlx可以认为是Go语言内置database/sql的超集，它在优秀的内置database/sql基础上提供了一组扩展。这些扩展中除了大家常用来查询的Get(dest interface&#123;&#125;, ...) error和Select(dest interface&#123;&#125;, ...) error外还有很多其他强大的功能。</span></span><br><span class="line"><span class="comment">*/</span></span><br><span class="line"></span><br><span class="line"><span class="comment">/*</span></span><br><span class="line"><span class="comment">连接数据库</span></span><br><span class="line"><span class="comment">    var db *sqlx.DB</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">    func initDB() (err error) &#123;</span></span><br><span class="line"><span class="comment">        dsn := &quot;user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&amp;parseTime=True&quot;</span></span><br><span class="line"><span class="comment">        // 也可以使用MustConnect连接不成功就panic</span></span><br><span class="line"><span class="comment">        db, err = sqlx.Connect(&quot;mysql&quot;, dsn)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;connect DB failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        db.SetMaxOpenConns(20)</span></span><br><span class="line"><span class="comment">        db.SetMaxIdleConns(10)</span></span><br><span class="line"><span class="comment">        return</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment">查询</span></span><br><span class="line"><span class="comment">查询单行数据示例代码如下：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// 查询单条数据示例</span></span><br><span class="line"><span class="comment">    func queryRowDemo() &#123;</span></span><br><span class="line"><span class="comment">        sqlStr := &quot;select id, name, age from user where id=?&quot;</span></span><br><span class="line"><span class="comment">        var u user</span></span><br><span class="line"><span class="comment">        err := db.Get(&amp;u, sqlStr, 1)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;get failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        fmt.Printf(&quot;id:%d name:%s age:%d\n&quot;, u.ID, u.Name, u.Age)</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment">查询多行数据示例代码如下：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// 查询多条数据示例</span></span><br><span class="line"><span class="comment">    func queryMultiRowDemo() &#123;</span></span><br><span class="line"><span class="comment">        sqlStr := &quot;select id, name, age from user where id &gt; ?&quot;</span></span><br><span class="line"><span class="comment">        var users []user</span></span><br><span class="line"><span class="comment">        err := db.Select(&amp;users, sqlStr, 0)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;query failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        fmt.Printf(&quot;users:%#v\n&quot;, users)</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment">插入、更新和删除</span></span><br><span class="line"><span class="comment">sqlx中的exec方法与原生sql中的exec使用基本一致：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// 插入数据</span></span><br><span class="line"><span class="comment">    func insertRowDemo() &#123;</span></span><br><span class="line"><span class="comment">        sqlStr := &quot;insert into user(name, age) values (?,?)&quot;</span></span><br><span class="line"><span class="comment">        ret, err := db.Exec(sqlStr, &quot;沙河小王子&quot;, 19)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;insert failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        theID, err := ret.LastInsertId() // 新插入数据的id</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;get lastinsert ID failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        fmt.Printf(&quot;insert success, the id is %d.\n&quot;, theID)</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// 更新数据</span></span><br><span class="line"><span class="comment">    func updateRowDemo() &#123;</span></span><br><span class="line"><span class="comment">        sqlStr := &quot;update user set age=? where id = ?&quot;</span></span><br><span class="line"><span class="comment">        ret, err := db.Exec(sqlStr, 39, 6)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;update failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        n, err := ret.RowsAffected() // 操作影响的行数</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;get RowsAffected failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        fmt.Printf(&quot;update success, affected rows:%d\n&quot;, n)</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// 删除数据</span></span><br><span class="line"><span class="comment">    func deleteRowDemo() &#123;</span></span><br><span class="line"><span class="comment">        sqlStr := &quot;delete from user where id = ?&quot;</span></span><br><span class="line"><span class="comment">        ret, err := db.Exec(sqlStr, 6)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;delete failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        n, err := ret.RowsAffected() // 操作影响的行数</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;get RowsAffected failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        fmt.Printf(&quot;delete success, affected rows:%d\n&quot;, n)</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment">NamedExec</span></span><br><span class="line"><span class="comment">DB.NamedExec方法用来绑定SQL语句与结构体或map中的同名字段。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">    func insertUserDemo()(err error)&#123;</span></span><br><span class="line"><span class="comment">        sqlStr := &quot;INSERT INTO user (name,age) VALUES (:name,:age)&quot;</span></span><br><span class="line"><span class="comment">        _, err = db.NamedExec(sqlStr,</span></span><br><span class="line"><span class="comment">            map[string]interface&#123;&#125;&#123;</span></span><br><span class="line"><span class="comment">                &quot;name&quot;: &quot;Ame&quot;,</span></span><br><span class="line"><span class="comment">                &quot;age&quot;: 28,</span></span><br><span class="line"><span class="comment">            &#125;)</span></span><br><span class="line"><span class="comment">        return</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment">NamedQuery</span></span><br><span class="line"><span class="comment">与DB.NamedExec同理，这里是支持查询。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">    func namedQuery()&#123;</span></span><br><span class="line"><span class="comment">        sqlStr := &quot;SELECT * FROM user WHERE name=:name&quot;</span></span><br><span class="line"><span class="comment">        // 使用map做命名查询</span></span><br><span class="line"><span class="comment">        rows, err := db.NamedQuery(sqlStr, map[string]interface&#123;&#125;&#123;&quot;name&quot;: &quot;Ame&quot;&#125;)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;db.NamedQuery failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        defer rows.Close()</span></span><br><span class="line"><span class="comment">        for rows.Next()&#123;</span></span><br><span class="line"><span class="comment">            var u user</span></span><br><span class="line"><span class="comment">            err := rows.StructScan(&amp;u)</span></span><br><span class="line"><span class="comment">            if err != nil &#123;</span></span><br><span class="line"><span class="comment">                fmt.Printf(&quot;scan failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">                continue</span></span><br><span class="line"><span class="comment">            &#125;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;user:%#v\n&quot;, u)</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">        u := user&#123;</span></span><br><span class="line"><span class="comment">            Name: &quot;Ame&quot;,</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">	// 使用结构体命名查询，根据结构体字段的 db tag进行映射</span></span><br><span class="line"><span class="comment">        rows, err = db.NamedQuery(sqlStr, u)</span></span><br><span class="line"><span class="comment">        if err != nil &#123;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;db.NamedQuery failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">            return</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">        defer rows.Close()</span></span><br><span class="line"><span class="comment">        for rows.Next()&#123;</span></span><br><span class="line"><span class="comment">            var u user</span></span><br><span class="line"><span class="comment">            err := rows.StructScan(&amp;u)</span></span><br><span class="line"><span class="comment">            if err != nil &#123;</span></span><br><span class="line"><span class="comment">                fmt.Printf(&quot;scan failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">                continue</span></span><br><span class="line"><span class="comment">            &#125;</span></span><br><span class="line"><span class="comment">            fmt.Printf(&quot;user:%#v\n&quot;, u)</span></span><br><span class="line"><span class="comment">        &#125;</span></span><br><span class="line"><span class="comment">    &#125;</span></span><br><span class="line"><span class="comment">事务操作</span></span><br><span class="line"><span class="comment">对于事务操作，我们可以使用sqlx中提供的db.Beginx()和tx.Exec()方法。示例代码如下：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">func transactionDemo2()(err error) &#123;</span></span><br><span class="line"><span class="comment">	tx, err := db.Beginx() // 开启事务</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		fmt.Printf(&quot;begin trans failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">		return err</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	defer func() &#123;</span></span><br><span class="line"><span class="comment">		if p := recover(); p != nil &#123;</span></span><br><span class="line"><span class="comment">			tx.Rollback()</span></span><br><span class="line"><span class="comment">			panic(p) // re-throw panic after Rollback</span></span><br><span class="line"><span class="comment">		&#125; else if err != nil &#123;</span></span><br><span class="line"><span class="comment">			fmt.Println(&quot;rollback&quot;)</span></span><br><span class="line"><span class="comment">			tx.Rollback() // err is non-nil; don&#x27;t change it</span></span><br><span class="line"><span class="comment">		&#125; else &#123;</span></span><br><span class="line"><span class="comment">			err = tx.Commit() // err is nil; if Commit returns error update err</span></span><br><span class="line"><span class="comment">			fmt.Println(&quot;commit&quot;)</span></span><br><span class="line"><span class="comment">		&#125;</span></span><br><span class="line"><span class="comment">	&#125;()</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	sqlStr1 := &quot;Update user set age=20 where id=?&quot;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	rs, err := tx.Exec(sqlStr1, 1)</span></span><br><span class="line"><span class="comment">	if err!= nil&#123;</span></span><br><span class="line"><span class="comment">		return err</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	n, err := rs.RowsAffected()</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		return err</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	if n != 1 &#123;</span></span><br><span class="line"><span class="comment">		return errors.New(&quot;exec sqlStr1 failed&quot;)</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	sqlStr2 := &quot;Update user set age=50 where i=?&quot;</span></span><br><span class="line"><span class="comment">	rs, err = tx.Exec(sqlStr2, 5)</span></span><br><span class="line"><span class="comment">	if err!=nil&#123;</span></span><br><span class="line"><span class="comment">		return err</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	n, err = rs.RowsAffected()</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		return err</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	if n != 1 &#123;</span></span><br><span class="line"><span class="comment">		return errors.New(&quot;exec sqlStr1 failed&quot;)</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	return err</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">sqlx.In</span></span><br><span class="line"><span class="comment">sqlx.In是sqlx提供的一个非常方便的函数。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">sqlx.In的批量插入示例</span></span><br><span class="line"><span class="comment">表结构</span></span><br><span class="line"><span class="comment">为了方便演示插入数据操作，这里创建一个user表，表结构如下：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">CREATE TABLE `user` (</span></span><br><span class="line"><span class="comment">    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,</span></span><br><span class="line"><span class="comment">    `name` VARCHAR(20) DEFAULT &#x27;&#x27;,</span></span><br><span class="line"><span class="comment">    `age` INT(11) DEFAULT &#x27;0&#x27;,</span></span><br><span class="line"><span class="comment">    PRIMARY KEY(`id`)</span></span><br><span class="line"><span class="comment">)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;</span></span><br><span class="line"><span class="comment">结构体</span></span><br><span class="line"><span class="comment">定义一个user结构体，字段通过tag与数据库中user表的列一致。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">type User struct &#123;</span></span><br><span class="line"><span class="comment">	Name string `db:&quot;name&quot;`</span></span><br><span class="line"><span class="comment">	Age  int    `db:&quot;age&quot;`</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">bindvars（绑定变量）</span></span><br><span class="line"><span class="comment">查询占位符?在内部称为bindvars（查询占位符）,它非常重要。你应该始终使用它们向数据库发送值，因为它们可以防止SQL注入攻击。database/sql不尝试对查询文本进行任何验证；它与编码的参数一起按原样发送到服务器。除非驱动程序实现一个特殊的接口，否则在执行之前，查询是在服务器上准备的。因此bindvars是特定于数据库的:</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">MySQL中使用?</span></span><br><span class="line"><span class="comment">PostgreSQL使用枚举的$1、$2等bindvar语法</span></span><br><span class="line"><span class="comment">SQLite中?和$1的语法都支持</span></span><br><span class="line"><span class="comment">Oracle中使用:name的语法</span></span><br><span class="line"><span class="comment">bindvars的一个常见误解是，它们用来在sql语句中插入值。它们其实仅用于参数化，不允许更改SQL语句的结构。例如，使用bindvars尝试参数化列或表名将不起作用：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// 不能用来插入表名（做SQL语句中表名的占位符）</span></span><br><span class="line"><span class="comment">db.Query(&quot;SELECT * FROM ?&quot;, &quot;mytable&quot;)</span></span><br><span class="line"><span class="comment"> </span></span><br><span class="line"><span class="comment">// 也不能用来插入列名（做SQL语句中列名的占位符）</span></span><br><span class="line"><span class="comment">db.Query(&quot;SELECT ?, ? FROM people&quot;, &quot;name&quot;, &quot;location&quot;)</span></span><br><span class="line"><span class="comment">自己拼接语句实现批量插入</span></span><br><span class="line"><span class="comment">比较笨，但是很好理解。就是有多少个User就拼接多少个(?, ?)。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// BatchInsertUsers 自行构造批量插入的语句</span></span><br><span class="line"><span class="comment">func BatchInsertUsers(users []*User) error &#123;</span></span><br><span class="line"><span class="comment">	// 存放 (?, ?) 的slice</span></span><br><span class="line"><span class="comment">	valueStrings := make([]string, 0, len(users))</span></span><br><span class="line"><span class="comment">	// 存放values的slice</span></span><br><span class="line"><span class="comment">	valueArgs := make([]interface&#123;&#125;, 0, len(users) * 2)</span></span><br><span class="line"><span class="comment">	// 遍历users准备相关数据</span></span><br><span class="line"><span class="comment">	for _, u := range users &#123;</span></span><br><span class="line"><span class="comment">		// 此处占位符要与插入值的个数对应</span></span><br><span class="line"><span class="comment">		valueStrings = append(valueStrings, &quot;(?, ?)&quot;)</span></span><br><span class="line"><span class="comment">		valueArgs = append(valueArgs, u.Name)</span></span><br><span class="line"><span class="comment">		valueArgs = append(valueArgs, u.Age)</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	// 自行拼接要执行的具体语句</span></span><br><span class="line"><span class="comment">	stmt := fmt.Sprintf(&quot;INSERT INTO user (name, age) VALUES %s&quot;,</span></span><br><span class="line"><span class="comment">		strings.Join(valueStrings, &quot;,&quot;))</span></span><br><span class="line"><span class="comment">	_, err := DB.Exec(stmt, valueArgs...)</span></span><br><span class="line"><span class="comment">	return err</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">使用sqlx.In实现批量插入</span></span><br><span class="line"><span class="comment">前提是需要我们的结构体实现driver.Valuer接口：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">func (u User) Value() (driver.Value, error) &#123;</span></span><br><span class="line"><span class="comment">	return []interface&#123;&#125;&#123;u.Name, u.Age&#125;, nil</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">使用sqlx.In实现批量插入代码如下：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface&#123;&#125;</span></span><br><span class="line"><span class="comment">func BatchInsertUsers2(users []interface&#123;&#125;) error &#123;</span></span><br><span class="line"><span class="comment">	query, args, _ := sqlx.In(</span></span><br><span class="line"><span class="comment">		&quot;INSERT INTO user (name, age) VALUES (?), (?), (?)&quot;,</span></span><br><span class="line"><span class="comment">		users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它</span></span><br><span class="line"><span class="comment">	)</span></span><br><span class="line"><span class="comment">	fmt.Println(query) // 查看生成的querystring</span></span><br><span class="line"><span class="comment">	fmt.Println(args)  // 查看生成的args</span></span><br><span class="line"><span class="comment">	_, err := DB.Exec(query, args...)</span></span><br><span class="line"><span class="comment">	return err</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">使用NamedExec实现批量插入</span></span><br><span class="line"><span class="comment">注意 ：该功能需1.3.1版本以上，并且1.3.1版本目前还有点问题，sql语句最后不能有空格和;，详见issues/690。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">使用NamedExec实现批量插入的代码如下：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// BatchInsertUsers3 使用NamedExec实现批量插入</span></span><br><span class="line"><span class="comment">func BatchInsertUsers3(users []*User) error &#123;</span></span><br><span class="line"><span class="comment">	_, err := DB.NamedExec(&quot;INSERT INTO user (name, age) VALUES (:name, :age)&quot;, users)</span></span><br><span class="line"><span class="comment">	return err</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">把上面三种方法综合起来试一下：</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">func main() &#123;</span></span><br><span class="line"><span class="comment">	err := initDB()</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		panic(err)</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	defer DB.Close()</span></span><br><span class="line"><span class="comment">	u1 := User&#123;Name: &quot;Ame&quot;, Age: 18&#125;</span></span><br><span class="line"><span class="comment">	u2 := User&#123;Name: &quot;q1mi&quot;, Age: 28&#125;</span></span><br><span class="line"><span class="comment">	u3 := User&#123;Name: &quot;小王子&quot;, Age: 38&#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	// 方法1</span></span><br><span class="line"><span class="comment">	users := []*User&#123;&amp;u1, &amp;u2, &amp;u3&#125;</span></span><br><span class="line"><span class="comment">	err = BatchInsertUsers(users)</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		fmt.Printf(&quot;BatchInsertUsers failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	// 方法2</span></span><br><span class="line"><span class="comment">	users2 := []interface&#123;&#125;&#123;u1, u2, u3&#125;</span></span><br><span class="line"><span class="comment">	err = BatchInsertUsers2(users2)</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		fmt.Printf(&quot;BatchInsertUsers2 failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	// 方法3</span></span><br><span class="line"><span class="comment">	users3 := []*User&#123;&amp;u1, &amp;u2, &amp;u3&#125;</span></span><br><span class="line"><span class="comment">	err = BatchInsertUsers3(users3)</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		fmt.Printf(&quot;BatchInsertUsers3 failed, err:%v\n&quot;, err)</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">sqlx.In的查询示例</span></span><br><span class="line"><span class="comment">关于sqlx.In这里再补充一个用法，在sqlx查询语句中实现In查询和FIND_IN_SET函数。即实现SELECT * FROM user WHERE id in (3, 2, 1);和SELECT * FROM user WHERE id in (3, 2, 1) ORDER BY FIND_IN_SET(id, &#x27;3,2,1&#x27;);。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">in查询</span></span><br><span class="line"><span class="comment">查询id在给定id集合中的数据。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// QueryByIDs 根据给定ID查询</span></span><br><span class="line"><span class="comment">func QueryByIDs(ids []int)(users []User, err error)&#123;</span></span><br><span class="line"><span class="comment">	// 动态填充id</span></span><br><span class="line"><span class="comment">	query, args, err := sqlx.In(&quot;SELECT name, age FROM user WHERE id IN (?)&quot;, ids)</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		return</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它</span></span><br><span class="line"><span class="comment">	query = DB.Rebind(query)</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	err = DB.Select(&amp;users, query, args...)</span></span><br><span class="line"><span class="comment">	return</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">in查询和FIND_IN_SET函数</span></span><br><span class="line"><span class="comment">查询id在给定id集合的数据并维持给定id集合的顺序。</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">// QueryAndOrderByIDs 按照指定id查询并维护顺序</span></span><br><span class="line"><span class="comment">func QueryAndOrderByIDs(ids []int)(users []User, err error)&#123;</span></span><br><span class="line"><span class="comment">	// 动态填充id</span></span><br><span class="line"><span class="comment">	strIDs := make([]string, 0, len(ids))</span></span><br><span class="line"><span class="comment">	for _, id := range ids &#123;</span></span><br><span class="line"><span class="comment">		strIDs = append(strIDs, fmt.Sprintf(&quot;%d&quot;, id))</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment">	query, args, err := sqlx.In(&quot;SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)&quot;, ids, strings.Join(strIDs, &quot;,&quot;))</span></span><br><span class="line"><span class="comment">	if err != nil &#123;</span></span><br><span class="line"><span class="comment">		return</span></span><br><span class="line"><span class="comment">	&#125;</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它</span></span><br><span class="line"><span class="comment">	query = DB.Rebind(query)</span></span><br><span class="line"><span class="comment"></span></span><br><span class="line"><span class="comment">	err = DB.Select(&amp;users, query, args...)</span></span><br><span class="line"><span class="comment">	return</span></span><br><span class="line"><span class="comment">&#125;</span></span><br><span class="line"><span class="comment">当然，在这个例子里面你也可以先使用IN查询，然后通过代码按给定的ids对查询结果进行排序。</span></span><br><span class="line"><span class="comment">*/</span></span><br></pre></td></tr></table></figure>

<h2 id="七、sql注入"><a href="#七、sql注入" class="headerlink" title="七、sql注入"></a>七、sql注入</h2><figure class="highlight go"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">我们不可以自己拼接字符串</span><br><span class="line"></span><br><span class="line">可以用预处理来防止sql注入</span><br></pre></td></tr></table></figure>


            </div>

            <!-- Post Comments -->
            
    <!-- 使用 DISQUS_CLICK -->
<div id="disqus-comment">
    <div id="disqus_thread"></div>

<!-- add animation -->
<style>
	.disqus_click_btn {
            line-height: 30px;
            margin: 0;
            min-width: 50px;
            padding: 0 14px;
            display: inline-block;
            font-family: "Roboto", "Helvetica", "Arial", sans-serif;
            font-size: 14px;
            font-weight: 400;
            text-transform: uppercase;
            letter-spacing: 0;
            overflow: hidden;
            will-change: box-shadow;
            transition: box-shadow .2s cubic-bezier(.4, 0, 1, 1), background-color .2s cubic-bezier(.4, 0, .2, 1), color .2s cubic-bezier(.4, 0, .2, 1);
            outline: 0;
            cursor: pointer;
            text-decoration: none;
            text-align: center;
            vertical-align: middle;
            border: 0;
            background: rgba(158, 158, 158, .2);
            box-shadow: 0 2px 2px 0 rgba(0, 0, 0, .14), 0 3px 1px -2px rgba(0, 0, 0, .2), 0 1px 5px 0 rgba(0, 0, 0, .12);
            color: #fff;
            background-color: #7EC0EE;
            text-shadow: 0
        }
</style>
	
<div class="btn_click_load" id="disqus_bt"> 
    <button class="disqus_click_btn">点击查看评论</button>
</div>

<!--
<script type="text/javascript">
$('.btn_click_load').click(function() {
    /* * * CONFIGURATION VARIABLES: EDIT BEFORE PASTING INTO YOUR WEBPAGE * * */
    var disqus_shortname = 'http-miccall-tech'; // required: replace example with your forum shortname

    /* * * DON'T EDIT BELOW THIS LINE * * */
    (function() {
      var dsq = document.createElement('script'); dsq.type = 'text/javascript'; dsq.async = true;
      dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
      (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
    })();

    document.getElementById('disqus_bt').style.display = "none";
});
</script>
-->
<script type="text/javascript">
    var disqus_config = function () {
        this.page.url = 'https://gitee.com/AmeX/amelin-ting.git/2018/05/17/Go%E6%95%B0%E6%8D%AE%E5%BA%93------MySQL/';  // Replace PAGE_URL with your page's canonical URL variable
        this.page.identifier = 'https://gitee.com/AmeX/amelin-ting.git/2018/05/17/Go%E6%95%B0%E6%8D%AE%E5%BA%93------MySQL/'; // Replace PAGE_IDENTIFIER with your page's unique identifier variable
    };
</script>

<script type="text/javascript">
    $('.btn_click_load').click(function() {  //click to load comments
        (function() { // DON'T EDIT BELOW THIS LINE
            var d = document;
            var s = d.createElement('script');
            s.src = '//http-miccall-tech.disqus.com/embed.js';
            s.setAttribute('data-timestamp', + new Date());
            (d.head || d.body).appendChild(s);
        })();
        $('.btn_click_load').css('display','none');
    });
</script>
</div>
<style>
    #disqus-comment{
        background-color: #eee;
        padding: 2pc;
    }
</style>


        </div>
        <!-- Copyright 版权 start -->
                <div id="copyright">
            <ul>
                <li>&copy;Powered By <a target="_blank" rel="noopener" href="https://hexo.io/zh-cn/" style="border-bottom: none;">hexo</a></li>
                <li>Design: <a target="_blank" rel="noopener" href="http://miccall.tech " style="border-bottom: none;">miccall</a></li>
            </ul>
            
                <span id="busuanzi_container_site_pv">本站总访问量<span id="busuanzi_value_site_pv"></span>次</span>
			
        </div>
    </div>
</body>



 	
</html>
